import os.path
from config.config import ALPHA_PORT,ALPHA_DB_HOST,DATA_PATH,DOWNLOAD_PATH,DESKTOP_PATH
from tools.mongo_tool import MongoDbLink
from tools.function import read_xlsx_file
import requests
from tools.function import write_xlsx
alpha = MongoDbLink(ALPHA_DB_HOST,ALPHA_PORT,'alpha')
bonded = MongoDbLink(ALPHA_DB_HOST,ALPHA_PORT,'bonded')
# stock_list = read_xlsx_file(os.path.join(DOWNLOAD_PATH,'超卖库存.xlsx'),1)
# orders = read_xlsx_file(os.path.join(DESKTOP_PATH,'extra超卖订单.xlsx'),5)
# bids = read_xlsx_file(os.path.join(DESKTOP_PATH,'extra超卖订单.xlsx'),6)
# ids = read_xlsx_file(os.path.join(DESKTOP_PATH,'extra超卖订单.xlsx'),9)
# orders = read_xlsx_file(os.path.join(DOWNLOAD_PATH,'3.11-3.17-订单上传失败补3.xlsx'),1)


# order_list = []
# for i in stock_list:
#     [order_list.append([z["merchantCode"],z['article_number'],z['properties'],z['sku_id'],z['stockId'],z['order_no'],
#                         z['seller_bidding_no'], z['sku_price']]) for z in bonded.find
#     ('orders',{"stockId": i,'order_status':{"$in":[1000,3030,3040,4000]}})]
#
# write_xlsx(['商家编号', '货号', '尺码', 'skuid', '库存id', '订单号', '出价单号', '价格'],
#            order_list,'超卖订单')

# order_id,bid_id = [],[]
# for z in orders:
#     [order_id.append(i['stockId']) for i in bonded.find('orders',{"order_no":str(z)})]
# for r in bids:
#     [bid_id.append(i['stockId']) for i in bonded.find('bids',{"bidding_no":str(r)})]
    
# for j in ids:
#     if j not in order_id:
#         print(j)

# if order_id == bid_id:
#     print(True)
# else:
#     print(False)

# li = []
# [li.append(i['_id']) for i in bonded.aggregate('stocks',[
#   {
#     '$lookup':
#       {
#         'from': "orders",
#         'localField': "_id",
#         'foreignField': "stockId",
#         'as': "result",
#       },
#   },
#   {
#     '$unwind':
#       {
#         'path': "$result",
#       },
#   },
#   {
#     '$match':
#       {
#         "result.orderGroups": {
#           '$in': [1, 3],
#         },
#       },
#   },
#   {
#     '$group':
#       {
#         '_id': "$_id",
#         'stock': {
#           '$avg': "$qty",
#         },
#         'order': {
#           '$sum': "$result.qty",
#         },
#       },
#   },
#   {
#     '$project':
#       {
#         'dif': {
#           '$subtract': ["$stock", "$order"],
#         },
#       },
#   },
#   {
#     '$match':
#       {
#         'dif': {
#          '$lt': 0,
#         },
#       },
#   },
# ])]
# stockid_list = ['wgTL5EchnC', '6GGWRgZQpf', 'PReEuFpvb7', 'hS2ueOuhSB', 'MZufKpAYzV', '05Dsueg1Gr', 'DGXNRDCHSR', 'ytQgRPaLyQ', 'qPpjkuOzZq', 'xuSmNFXDUj', 'oKi2Owuma8', '09hEcMmwBz', 'RuR999Hr40', 'AFrRsu12Tg', 'pVaucyiwTz', 'ph3hCkrFEj', 'bV9FuuEvs3', 'uug7NCmwHz', 'ry5dNmy8bS', 'XMqVS1XrJi', '0BYLITJzcO', 'ITpjxC0vDz', '5gGDLSUfkn', 'nSHzWotzBC', 'Nd4QC7xMKg', 'ZJKjF3OJ6l', 'r7eiu61QuF', 'cGSK7RBEI3', 'ocS4kdPPjb', 'ZAansIn2Tt', 'oOupqAYFsk', 'iiiVp1FZJs', 'sMbYSFTUQC', 'IF66bh7ybb', 'B0UgJD8kaO', 'uiy7KzeK5f', 'UxrGhz5sg1', 'qZoQN3GQCW', 'tQRcLvBwYx', 'Nli5jdshwq', '6CYUcEKy9Q', 'aIAY3B0kPk', 'dAUurobbwN', 'r5iRN8BfoM', 'Lp5vK7QnDD', 'qm920tSZS8', 'J3hCRM1bec', '7dB2ZpdSKR', 'BBwRS5TFxh', 'yd3VrbUSN9', 'R7n5gcHUdA', 'zigiKeI8kx', 'lcd8SdtEE3', 'JnkIAnv97P', '29kC7eXpcz', 'xWq1lfXu2E', '2O1SV4v46h', 'xqF3kilNqE', 'n3PDz8B768', 'Rk16YiORZ6', 'CprFUdMWIj']
# print(len(stockid_list))

## 集物对库存
# date_list = [[1688140800000,1690819200000],[1690819200000,1693497600000],[1693497600000,1696089600000],
#              [1696089600000,1698768000000],[1698768000000,1701360000000],[1701360000000,1704038400000],
#              [1704038400000,1706716800000],[1706716800000,1709222400000],[1709222400000,1711900800000]]
# order = []
# s=7
# for i in date_list:
#     [order.append(z['qty']) for z in alpha.find('stock_out_records',{'createdAt':{'$gt':i[0],'$lt':i[1]}, 'spuId': 12241,'outStockType':3})]
#     print(s,sum(order))
#     order = []
#     s+=1
#
# returns = []
# d=7
# for i in date_list:
#     [order.append(z['qty']) for z in alpha.find('stock_in_records',{'createdAt':{'$gt':i[0],'$lt':i[1]}, 'spuId': 12241,'appointType':2,'warehouseType':"common"})]
#     if d > 12:
#         d = d-12
#     print(f'{d}月',sum(order))
#     order = []
#     d+=1
## 集物对库存

## 保税超卖订单
# stockid_list = ['wgTL5EchnC', '6GGWRgZQpf', 'PReEuFpvb7', 'hS2ueOuhSB', 'MZufKpAYzV', '05Dsueg1Gr', 'DGXNRDCHSR', 'ytQgRPaLyQ', 'qPpjkuOzZq', 'xuSmNFXDUj', 'oKi2Owuma8', '09hEcMmwBz', 'RuR999Hr40', 'AFrRsu12Tg', 'pVaucyiwTz', 'ph3hCkrFEj', 'bV9FuuEvs3', 'uug7NCmwHz', 'ry5dNmy8bS', 'XMqVS1XrJi', '0BYLITJzcO', 'ITpjxC0vDz', '5gGDLSUfkn', 'nSHzWotzBC', 'Nd4QC7xMKg', 'ZJKjF3OJ6l', 'r7eiu61QuF', 'cGSK7RBEI3', 'ocS4kdPPjb', 'ZAansIn2Tt', 'oOupqAYFsk', 'iiiVp1FZJs', 'sMbYSFTUQC', 'IF66bh7ybb', 'B0UgJD8kaO', 'uiy7KzeK5f', 'UxrGhz5sg1', 'qZoQN3GQCW', 'tQRcLvBwYx', 'Nli5jdshwq', '6CYUcEKy9Q', 'aIAY3B0kPk', 'dAUurobbwN', 'r5iRN8BfoM', 'Lp5vK7QnDD', 'qm920tSZS8', 'J3hCRM1bec', '7dB2ZpdSKR', 'BBwRS5TFxh', 'yd3VrbUSN9', 'R7n5gcHUdA', 'zigiKeI8kx', 'lcd8SdtEE3', 'JnkIAnv97P', '29kC7eXpcz', 'xWq1lfXu2E', '2O1SV4v46h', 'xqF3kilNqE', 'n3PDz8B768', 'Rk16YiORZ6', 'CprFUdMWIj']
# li = []
# for stock in stockid_list:
#     [li.append([i['merchantCode'],i['article_number'],i['properties'],i['sku_id'],i['stockId'],i['order_no'],i['seller_bidding_no'],i['sku_price']/100]) for i in bonded.find_limit('orders',{'stockId': stock, 'orderGroups':{'$in':[1,3]}},0,1)]
# write_xlsx(['商家编号','货号','尺码','skuid','库存id','订单号','出价单号','价格'],li,'extra超卖订单-0415')
## 保税超卖订单
